Downloading and Executing Solution File (Dim_Mortgage) 10

·                 NOTE

We already done one Dimension Table (Dim_Contract).

We provided Solution File for the remaining three Dimension tables.

                 ·    Download the Solution Files that are Provided for the remaining 3-Dimensional Tables.

·                 ·    In the code file there are 3 separate files

                 ·    Download one package at a time (One by One)

                 ·     Right click on SSIS Packages ----> Select add Existing Package

               


·   A Dialogue Box with an option Add Copy of Existing Package appears ----> click on to specify the Path for the Solution File


·                    ·  Open File one at a time (Dim_Mortgage) form the Solution File provided
 

·                    ·    Make sure Path for the Solution File is provided ------> Click OK


 Note: Repeat the Steps for Dim_Owner and Dim_Property


 ·                  · All the packages are loaded.

                     ·       Double Click on Dim_Mortage.dtsx 

                  


·                   ·   Double click on the Data Flow Task to provide Source Connection 


·                   ·     When Double clicked on the Data Flow Task Package appears as below
 

 ·      Again Double click on the OLE DB Source - Mortgage Staging View to provide Source Connection



 ·     Click on New to provide New Source Connection Manager


 ·     Make sure New Connection Manager (ColaberryDatabase) is Provided                       
 ·     Then Click OK
   



 ·      Once created Connection Manager (ColaberryDatabase) is provided

·                   ·      Make sure that the Select Statement is available in the SQL Command Text

                   ·       Click OK

                 


Note: Repeat the Steps for Dim_Owner and Dim_Property

     

                  ·     Double click on First Lookup Dim_Mortgage to provide the Connection for Lookup


 ·         Lookup transformation Editor page will pop up like below.

 

 ·         Select Connection then Select New to provide the Connection for the (Lookup Dim_Mortgage)


 ·         Make sure the right Destination Connection Manager (Mortgage Datamart) is Provided

 ·         Then click OK


 ·     Make sure the Select Statement is available in the Use Results of an SQL Query. Click Ok



Note: Repeat the steps for Dim_Owner and Dim_Property, Make sure

1) Dim_Owner, In results of an SQL Query ---- > Select OwnerID from Dim_Owner

2) Dim_Property, In results of an SQL Query ---- > Select PropertyID from Dim_Property


·         Double click on OLE DB Destination - Inserting new records



·         Provide the right Connection Manager (Mortgage Datamart)

·         Click OK



·         Provide the Target Table (Dim_Mortgage)

·         Click OK



Note :Repeat the Steps for 1) Dim_Owner : In the Drop down of Name of the Table or View, Choose Dim_Owner

2) Dim_Property : In the Drop down of Name of the Table or View, Choose Dim_Property
 

·         Double click on the Second Lookup, (Lookup - Only Updatable records will pass through)

·         Click OK


·         Lookup transformation Editor page will pop up like below.


·         Provide the Connection Manager (Mortgage Datamart) for the Second Lookup

·         Click Connections ---->Click on the Drop Down of OLE DB Connection Manager and Choose (Mortgage Datamart) ----- >  Click OK


·         Click on the Drop Down of Name of the Table or View ---- > Choose Dim_Mortgage

 


Note :Repeat the Steps for 1) Dim_Owner : In the Drop down of use a Table or View, Choose Dim_Owner

2) Dim_Property : In the Drop down of use a  Table or View, Choose Dim_Property 


·                   ·         Double Click on the OLE DB Command (OLE DB Command - Updating Existing Records)

·         Select Connection Managers

·         Click on the Drop-down arrow

·         Provide OLE DB Connection Manager (Mortgage Datamart)



·                       Select the Component Properties

·                       Make Sure Exec Statement is in SQL Command ---- > Click OK

·              

Note: Repeat the Steps for

1)Dim_Owner (SQL Command: Exec     [dbo].[USP_UpdateDim_Owner] ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

2)Dim_Property (SQL Command : Exec [dbo].[USP_UpdateDim_Property] ?,?,?,?,?,?,?,?,?,?,?,?,?)


·         Package appears as shown below

 ·                  Delete the two Connection Managers that came with the Solution File    

                ·                  (LAPTOP-GCON8HFS\CB2016SQLSERVER.ColaberryDatabase) and  
                   (LAPTOP-GCON8HFS\CB2016SQLSERVER.Mortgage Datamart)

                

                ·         Right Click on LAPTOP-GCON8HFS\CB2016SQLSERVER.ColaberryDatabase) and Select Delete

                 

                ·         Right Click on (LAPTOP-GCON8HFS\CB2016SQLSERVER.Mortgage Datamart) and Select Delete

                

                

How to download Solution File?

https://docs.microsoft.com/en-us/sql/integration-services/integration-services-ssis-projects-and-solutions?view=sql-server-2017